/* 
 * table: accountcode 
 */

create table accountcode(
    id                   int             identity(1, 1)        not null,
    accountcodedesc      varchar(255)    null,
    accountcode          int             null,
    usageinstructions    varchar(255)    null,
    createdate           datetime        null,
    createdby            int             null,
    lastupdated          datetime        null,
    lastupdatedby        int             null,
    constraint pk28 primary key nonclustered (id)
)
go



if object_id('accountcode') is not null
    print '<<< created table accountcode >>>'
else
    print '<<< failed creating table accountcode >>>'
go

/* 
 * table: actionaltertchangenotifications 
 */

create table actionaltertchangenotifications(
    id               int              identity(1, 1)        not null,
    module           varchar(50)      null,
    noticetype       varchar(15)      null,
    noticedate       datetime         null,
    message          varchar(1000)    null,
    noticequery      varchar(4000)    null,
    createdate       datetime         null,
    createdby        int              null,
    lastupdatedby    int              null,
    lastupdated      datetime         null,
    completed        varchar(1)       null,
    alerttypeid      int              null,
    changetypeid     int              null,
    constraint pk55 primary key nonclustered (id)
)
go



if object_id('actionaltertchangenotifications') is not null
    print '<<< created table actionaltertchangenotifications >>>'
else
    print '<<< failed creating table actionaltertchangenotifications >>>'
go

/* 
 * table: alerttype 
 */

create table alerttype(
    id               int             identity(1, 1)        not null,
    typename         varchar(100)    null,
    createdate       datetime        null,
    createdby        int             null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    constraint pk8_1 primary key nonclustered (id)
)
go



if object_id('alerttype') is not null
    print '<<< created table alerttype >>>'
else
    print '<<< failed creating table alerttype >>>'
go

/* 
 * table: audits 
 */

create table audits(
    id                                       int               identity(1, 1)        not null,
    createdate                               datetime          null,
    createdby                                int               null,
    lastupdated                              datetime          null,
    lastupdatedby                            int               null,
    impaction                                varchar(50)       null,
    impduedate                               datetime          null,
    impperformed                             datetime          null,
    impreceived                              datetime          null,
    impclosedtofile                          datetime          null,
    year                                     int               null,
    acronym                                  varchar(50)       null,
    newgrantee                               varchar(1)        null,
    cipeorgranteesolicitsaudit               varchar(50)       null,
    riskanalysisrecdpo                       datetime          null,
    riskassessment                           numeric(18, 3)    null,
    audittype                                varchar(50)       null,
    smponly                                  varchar(1)        null,
    imp                                      varchar(1)        null,
    interimaudit                             varchar(1)        null,
    interimselectionmemoduedate              datetime          null,
    interimselectionmemoreceived             datetime          null,
    iauditorapproved                         datetime          null,
    interimauditfirm                         varchar(50)       null,
    iaduedate                                datetime          null,
    iareceived                               datetime          null,
    iaapproved                               datetime          null,
    iareturnedforcorrections                 datetime          null,
    revisediadue                             datetime          null,
    revisediareceived                        datetime          null,
    revisediaapproved                        datetime          null,
    iaclosedtofile                           datetime          null,
    iaresolutionoffindingsdue                datetime          null,
    iaresolutionoffindingsreceived           datetime          null,
    iaresolutionoffindingsapproved           datetime          null,
    iarevisedresolutionoffindingsdue         datetime          null,
    iarevisedresolutionreceived              datetime          null,
    iarevisedresolutionapproved              datetime          null,
    interimauditmemonotes                    varchar(5000)     null,
    finalaudit                               varchar(1)        null,
    faselectionmemoduedate                   datetime          null,
    faselectionmemoreceived                  datetime          null,
    finalauditorapproved                     datetime          null,
    finalauditfirm                           varchar(50)       null,
    datefinalauditdue                        datetime          null,
    datefinalauditreceived                   datetime          null,
    faapproved                               datetime          null,
    fareturnedforcorrections                 varchar(50)       null,
    revisedfadue                             datetime          null,
    revisedfinalauditreceived                datetime          null,
    revisedfaapproved                        varchar(50)       null,
    faresolutionoffindingsdue                datetime          null,
    faresolutionoffindingsreceived           datetime          null,
    faresolutionoffindingsapproved           datetime          null,
    farevisedresolutionoffindingsdue         datetime          null,
    farevisedresolutionoffindingsreceived    datetime          null,
    farevisedresolutionapproved              datetime          null,
    faclosedtofile                           datetime          null,
    finalauditmemoresolution                 varchar(5000)     null,
    impnotes                                 varchar(5000)     null,
    projectid                                int               null,
    constraint pk50 primary key nonclustered (id)
)
go



if object_id('audits') is not null
    print '<<< created table audits >>>'
else
    print '<<< failed creating table audits >>>'
go

/* 
 * table: categorylinks 
 */

create table categorylinks(
    id                int    identity(1, 1)        not null,
    linkid            int    null,
    linkcategoryid    int    null,
    constraint pk73 primary key nonclustered (id)
)
go



if object_id('categorylinks') is not null
    print '<<< created table categorylinks >>>'
else
    print '<<< failed creating table categorylinks >>>'
go

/* 
 * table: changenotificationtype 
 */

create table changenotificationtype(
    id               int             identity(1, 1)        not null,
    typename         varchar(100)    null,
    createdate       datetime        null,
    createdby        int             null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    constraint pk8_1_1 primary key nonclustered (id)
)
go



if object_id('changenotificationtype') is not null
    print '<<< created table changenotificationtype >>>'
else
    print '<<< failed creating table changenotificationtype >>>'
go

/* 
 * table: country 
 */

create table country(
    id                  int             identity(1, 1)        not null,
    countryname         varchar(100)    null,
    createdate          datetime        null,
    createdby           int             null,
    lastupdated         datetime        null,
    lastupdatedby       int             null,
    regionid            int             null,
    programassistant    int             null,
    programofficer      int             null,
    constraint pk20 primary key nonclustered (id)
)
go



if object_id('country') is not null
    print '<<< created table country >>>'
else
    print '<<< failed creating table country >>>'
go

/* 
 * table: donor 
 */

create table donor(
    id               int             identity(1, 1)        not null,
    donorname        varchar(100)    null,
    donoracronym     varchar(100)    null,
    url              varchar(400)    null,
    address1         varchar(100)    null,
    address2         varchar(100)    null,
    city             varchar(50)     null,
    state            varchar(25)     null,
    zipcode          varchar(20)     null,
    contactname      varchar(100)    null,
    contactphone     varchar(20)     null,
    contactfax       varchar(20)     null,
    contactemail     varchar(100)    null,
    createdby        int             null,
    createdate       datetime        null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    countryid        int             null,
    constraint pk1 primary key nonclustered (id)
)
go



if object_id('donor') is not null
    print '<<< created table donor >>>'
else
    print '<<< failed creating table donor >>>'
go

/* 
 * table: donoraward 
 */

create table donoraward(
    id                       int               identity(1, 1)        not null,
    donorawardnumber         varchar(50)       null,
    donorawarddesc           varchar(500)      null,
    startdate                datetime          null,
    enddate                  datetime          null,
    attachment               varchar(512)      null,
    fiscalyear               int               null,
    attachmentname           varchar(100)      null,
    attachmentcontenttype    varchar(100)      null,
    createdby                int               null,
    createdate               datetime          null,
    lastupdated              datetime          null,
    lastupdatedby            int               null,
    obligedamount            numeric(18, 3)    null,
    memonotes                varchar(5000)     null,
    directfunds              int               null,
    indirectfunds            int               null,
    donorid                  int               null,
    donorawardtypeid         int               null,
    donorawardstatusid       int               null,
    constraint pk3 primary key nonclustered (id)
)
go



if object_id('donoraward') is not null
    print '<<< created table donoraward >>>'
else
    print '<<< failed creating table donoraward >>>'
go

/* 
 * table: donorawardmodification 
 */

create table donorawardmodification(
    id                    int             identity(1, 1)        not null,
    previousvalue         varchar(15)     null,
    newvalue              varchar(15)     null,
    attachement           varchar(512)    null,
    createdate            datetime        null,
    createdby             int             null,
    lastupdated           datetime        null,
    lastupdatedby         int             null,
    donorawardid          int             null,
    modificationtypeid    int             null,
    constraint pk15_1 primary key nonclustered (id)
)
go



if object_id('donorawardmodification') is not null
    print '<<< created table donorawardmodification >>>'
else
    print '<<< failed creating table donorawardmodification >>>'
go

/* 
 * table: donorawardstatus 
 */

create table donorawardstatus(
    id               int             identity(1, 1)        not null,
    statusname       varchar(100)    null,
    createdby        int             null,
    createdate       datetime        null,
    lastupdatedby    int             null,
    lastupdated      datetime        null,
    constraint pk52 primary key nonclustered (id)
)
go



if object_id('donorawardstatus') is not null
    print '<<< created table donorawardstatus >>>'
else
    print '<<< failed creating table donorawardstatus >>>'
go

/* 
 * table: donorawardstatustransitionsallowed 
 */

create table donorawardstatustransitionsallowed(
    id                    int         identity(1, 1)        not null,
    createdby             int         null,
    createdate            datetime    null,
    lastupdatedby         int         null,
    lastupdatedate        datetime    null,
    goingtostatus         int         null,
    startingfromstatus    int         null,
    constraint pk65_1_2 primary key nonclustered (id)
)
go



if object_id('donorawardstatustransitionsallowed') is not null
    print '<<< created table donorawardstatustransitionsallowed >>>'
else
    print '<<< failed creating table donorawardstatustransitionsallowed >>>'
go

/* 
 * table: donorawardtype 
 */

create table donorawardtype(
    id                    int             identity(1, 1)        not null,
    donorawardtypedesc    varchar(500)    null,
    donorawardtype        varchar(100)    null,
    createdby             int             null,
    createdate            datetime        null,
    lastupdated           datetime        null,
    lastupdatedby         int             null,
    deleted               varchar(1)      null,
    constraint pk2 primary key nonclustered (id)
)
go



if object_id('donorawardtype') is not null
    print '<<< created table donorawardtype >>>'
else
    print '<<< failed creating table donorawardtype >>>'
go

/* 
 * table: fieldoffice 
 */

create table fieldoffice(
    id                 int             identity(1, 1)        not null,
    fieldofficecode    varchar(25)     null,
    fieldofficename    varchar(100)    null,
    address1           varchar(100)    null,
    address2           varchar(100)    null,
    city               varchar(50)     null,
    contactemail       varchar(100)    null,
    contactname        varchar(100)    null,
    contactphone       varchar(20)     null,
    contactfax         varchar(20)     null,
    emailaddress       varchar(100)    null,
    phone              varchar(20)     null,
    fax                varchar(20)     null,
    stateprovince      varchar(25)     null,
    inactive           varchar(1)      null,
    createdate         datetime        null,
    createdby          int             null,
    lastupdated        datetime        null,
    lastupdatedby      int             null,
    countryid          int             null,
    regionid           int             null,
    constraint pk40 primary key nonclustered (id)
)
go



if object_id('fieldoffice') is not null
    print '<<< created table fieldoffice >>>'
else
    print '<<< failed creating table fieldoffice >>>'
go

/* 
 * table: imp 
 */

create table imp(
    id                  int            identity(1, 1)        not null,
    createdate          datetime       null,
    createdby           int            null,
    lastupdated         datetime       null,
    lastupdatedby       int            null,
    impaction           varchar(50)    null,
    impduedate          datetime       null,
    impperformed        datetime       null,
    impreceived         datetime       null,
    impclosedtofile     datetime       null,
    projectreportsid    int            null,
    constraint pk50_1 primary key nonclustered (id)
)
go



if object_id('imp') is not null
    print '<<< created table imp >>>'
else
    print '<<< failed creating table imp >>>'
go

/* 
 * table: keyword 
 */

create table keyword(
    id               int             identity(1, 1)        not null,
    keywordcode      varchar(30)     null,
    keyworddesc      varchar(500)    null,
    createdby        int             null,
    createdate       datetime        null,
    lastupdatedby    int             null,
    lastupdated      datetime        null,
    constraint pk59 primary key nonclustered (id)
)
go



if object_id('keyword') is not null
    print '<<< created table keyword >>>'
else
    print '<<< failed creating table keyword >>>'
go

/* 
 * table: link 
 */

create table link(
    id              int             identity(1, 1)        not null,
    displayorder    int             null,
    displayname     varchar(500)    null,
    url             varchar(400)    null,
    createdate      datetime        null,
    createdby       int             null,
    lastupdated     datetime        null,
    lastupdateby    int             null,
    constraint pk44_1 primary key nonclustered (id)
)
go



if object_id('link') is not null
    print '<<< created table link >>>'
else
    print '<<< failed creating table link >>>'
go

/* 
 * table: linkcategory 
 */

create table linkcategory(
    id              int             identity(1, 1)        not null,
    displayorder    int             null,
    displayname     varchar(500)    null,
    createdate      datetime        null,
    createdby       int             null,
    lastupdated     datetime        null,
    lastupdateby    int             null,
    constraint pk44_1_1 primary key nonclustered (id)
)
go



if object_id('linkcategory') is not null
    print '<<< created table linkcategory >>>'
else
    print '<<< failed creating table linkcategory >>>'
go

/* 
 * table: modificationtype 
 */

create table modificationtype(
    id                      int             identity(1, 1)        not null,
    modificationtypecode    varchar(25)     null,
    modificationdesc        varchar(500)    null,
    createdate              datetime        null,
    createdby               int             null,
    lastupdated             datetime        null,
    lastupdatedby           int             null,
    constraint pk15_1_2 primary key nonclustered (id)
)
go



if object_id('modificationtype') is not null
    print '<<< created table modificationtype >>>'
else
    print '<<< failed creating table modificationtype >>>'
go

/* 
 * table: organizationtype 
 */

create table organizationtype(
    id                      int             identity(1, 1)        not null,
    organizationtypename    varchar(100)    null,
    organizationtypedesc    varchar(500)    null,
    createdby               int             null,
    createdate              datetime        null,
    lastupdatedby           int             null,
    lastupdated             datetime        null,
    constraint pk59_1_1_1 primary key nonclustered (id)
)
go



if object_id('organizationtype') is not null
    print '<<< created table organizationtype >>>'
else
    print '<<< failed creating table organizationtype >>>'
go

/* 
 * table: partner 
 */

create table partner(
    id                   int             identity(1, 1)        not null,
    createdate           datetime        null,
    createdby            int             null,
    lastupdated          datetime        null,
    lastupdatedby        int             null,
    partnername          varchar(100)    null,
    address1             varchar(100)    null,
    address2             varchar(100)    null,
    city                 varchar(50)     null,
    state                varchar(25)     null,
    zipcode              varchar(20)     null,
    phone                varchar(20)     null,
    fax                  varchar(20)     null,
    email                varchar(50)     null,
    url                  varchar(400)    null,
    contactfirstname     varchar(100)    null,
    contactmiddlename    varchar(100)    null,
    contactlastname      varchar(100)    null,
    contactphone         varchar(20)     null,
    contactfax           varchar(20)     null,
    contactemail         varchar(50)     null,
    countryid            int             null,
    constraint pk11 primary key nonclustered (id)
)
go



if object_id('partner') is not null
    print '<<< created table partner >>>'
else
    print '<<< failed creating table partner >>>'
go

/* 
 * table: payee 
 */

create table payee(
    id                         int             identity(1, 1)        not null,
    payeename                  varchar(50)     null,
    payeeaddress1              varchar(100)    null,
    payeetype                  varchar(25)     null,
    payeeaddress2              varchar(100)    null,
    payeecity                  varchar(50)     null,
    payeestate                 varchar(25)     null,
    payeezipcode               varchar(20)     null,
    payeeemail                 varchar(100)    null,
    payeephone                 varchar(20)     null,
    payeefax                   varchar(20)     null,
    payeeurl                   varchar(400)    null,
    beneficiarytname           varchar(100)    null,
    beneficiaryaddressline1    varchar(100)    null,
    beneficiaryaddressline2    varchar(100)    null,
    beneficiarycity            varchar(50)     null,
    beneficiarystate           varchar(25)     null,
    beneficiaryzipcode         varchar(20)     null,
    beneficiarycountrycode     varchar(3)      null,
    phonenumber                varchar(20)     null,
    bankname                   varchar(100)    null,
    bankcode                   varchar(25)     null,
    chipscode                  varchar(25)     null,
    accountnumber              varchar(25)     null,
    bankaddressline1           varchar(100)    null,
    bankaddressline2           varchar(100)    null,
    bankcity                   varchar(50)     null,
    bankstate                  varchar(25)     null,
    bankzipcode                varchar(20)     null,
    bankcountrycode            varchar(25)     null,
    intermediarybank           varchar(100)    null,
    intermediarybankcode       varchar(25)     null,
    specialinstructions        varchar(200)    null,
    createdate                 datetime        null,
    createdby                  int             null,
    lastupdated                datetime        null,
    lastupdatedby              int             null,
    countryid                  int             null,
    constraint pk24 primary key nonclustered (id)
)
go



if object_id('payee') is not null
    print '<<< created table payee >>>'
else
    print '<<< failed creating table payee >>>'
go

/* 
 * table: paymentrequest 
 */

create table paymentrequest(
    id                              int               identity(1, 1)        not null,
    paymentidentifiedinformation    varchar(500)      null,
    receivedfromaccountingdate      datetime          null,
    paymentconfirmationnumber       varchar(50)       null,
    purposeofexpenditure            varchar(500)      null,
    paymentmethod                   varchar(50)       null,
    specialinstructions             varchar(200)      null,
    statuscode                      varchar(50)       null,
    createdate                      datetime          null,
    createdby                       int               null,
    lastupdated                     datetime          null,
    lastupdatedby                   int               null,
    memonotes                       varchar(5000)     null,
    deliverablesreceived            varchar(1)        null,
    invoicenumber                   varchar(50)       null,
    invoicedate                     datetime          null,
    amount                          numeric(18, 3)    null,
    paidamount                      numeric(18, 3)    null,
    comments                        varchar(1000)     null,
    dateinfinance                   datetime          null,
    daterejected                    datetime          null,
    dateresubmitted                 datetime          null,
    dateforwardedforsignature       datetime          null,
    pendingpaymentdate              datetime          null,
    datecancelled                   datetime          null,
    datepaid                        datetime          null,
    currentlyresponsible            varchar(100)      null,
    rejectreason                    varchar(500)      null,
    paymenttypeid                   int               null,
    payeeid                         int               null,
    countryid                       int               null,
    constraint pk22 primary key nonclustered (id)
)
go



if object_id('paymentrequest') is not null
    print '<<< created table paymentrequest >>>'
else
    print '<<< failed creating table paymentrequest >>>'
go

/* 
 * table: paymentrequestlineitem 
 */

create table paymentrequestlineitem(
    id                      int               identity(1, 1)        not null,
    lineitemnumber          varchar(50)       null,
    paymentrequestamount    numeric(15, 2)    null,
    createdate              datetime          null,
    createdby               int               null,
    lastupdated             datetime          null,
    lastupdatedby           int               null,
    paymentrequestid        int               null,
    accountcodeid           int               null,
    projectid               int               null,
    constraint pk27 primary key nonclustered (id)
)
go



if object_id('paymentrequestlineitem') is not null
    print '<<< created table paymentrequestlineitem >>>'
else
    print '<<< failed creating table paymentrequestlineitem >>>'
go

/* 
 * table: paymentrequestmodification 
 */

create table paymentrequestmodification(
    id                    int             identity(1, 1)        not null,
    previousvalue         varchar(15)     null,
    newvalue              varchar(15)     null,
    attachement           varchar(512)    null,
    createdate            datetime        null,
    createdby             int             null,
    lastupdated           datetime        null,
    lastupdatedby         int             null,
    paymentrequestid      int             null,
    modificationtypeid    int             null,
    constraint pk15_1_1 primary key nonclustered (id)
)
go



if object_id('paymentrequestmodification') is not null
    print '<<< created table paymentrequestmodification >>>'
else
    print '<<< failed creating table paymentrequestmodification >>>'
go

/* 
 * table: paymentrequeststatus 
 */

create table paymentrequeststatus(
    id                     int            identity(1, 1)        not null,
    effectivedate          datetime       null,
    createdate             datetime       null,
    createdby              int            null,
    lastupdated            datetime       null,
    lastupdatedby          int            null,
    previousvalue          varchar(15)    null,
    newvalue               varchar(15)    null,
    paymentstatustypeid    int            null,
    paymentrequestid       int            null,
    constraint pk26 primary key nonclustered (id)
)
go



if object_id('paymentrequeststatus') is not null
    print '<<< created table paymentrequeststatus >>>'
else
    print '<<< failed creating table paymentrequeststatus >>>'
go

/* 
 * table: paymentrequeststatustransitions 
 */

create table paymentrequeststatustransitions(
    id                      int             identity(1, 1)        not null,
    createdby               int             null,
    createdate              datetime        null,
    lastupdatedby           int             null,
    lastupdatedate          datetime        null,
    statustransitiondesc    varchar(500)    null,
    goingtostatus           int             null,
    startingfromstatus      int             null,
    constraint pk65_1_1 primary key nonclustered (id)
)
go



if object_id('paymentrequeststatustransitions') is not null
    print '<<< created table paymentrequeststatustransitions >>>'
else
    print '<<< failed creating table paymentrequeststatustransitions >>>'
go

/* 
 * table: paymentstatustype 
 */

create table paymentstatustype(
    id                   int             identity(1, 1)        not null,
    paymentstatusname    varchar(100)    null,
    paymentstatusdesc    varchar(500)    null,
    createdate           datetime        null,
    createdby            int             null,
    lastupdated          datetime        null,
    lastupdatedby        int             null,
    constraint pk25 primary key nonclustered (id)
)
go



if object_id('paymentstatustype') is not null
    print '<<< created table paymentstatustype >>>'
else
    print '<<< failed creating table paymentstatustype >>>'
go

/* 
 * table: paymenttype 
 */

create table paymenttype(
    id                 int             identity(1, 1)        not null,
    paymenttypename    varchar(100)    null,
    paymenttypedesc    varchar(500)    null,
    createdate         datetime        null,
    createdby          int             null,
    lastupdated        datetime        null,
    lastupdatedby      int             null,
    constraint pk21 primary key nonclustered (id)
)
go



if object_id('paymenttype') is not null
    print '<<< created table paymenttype >>>'
else
    print '<<< failed creating table paymenttype >>>'
go

/* 
 * table: project 
 */

create table project(
    id                                 int               identity(1, 1)        not null,
    parentprojectid                    int               null,
    masterprojectid                    int               null,
    alphaidentifier                    varchar(2)        null,
    costcenternumber                   varchar(50)       null,
    projectstartdate                   datetime          null,
    projectenddate                     datetime          null,
    nedidentifier                      varchar(50)       null,
    matchingfunds                      numeric(15, 2)    null,
    managementfunds                    numeric(15, 2)    null,
    approvalroundmonth                 varchar(25)       null,
    approvalroundyear                  varchar(25)       null,
    createdate                         datetime          null,
    createdby                          int               null,
    lastupdated                        datetime          null,
    lastupdatedby                      int               null,
    acronym                            varchar(50)       null,
    projecttitle                       varchar(100)      null,
    partnergrantfunds                  numeric(15, 2)    null,
    memo                               varchar(5000)     null,
    receiptofsignedagreement           datetime          null,
    receiptofsignatureauthorization    datetime          null,
    receiptofbankinformation           datetime          null,
    override                           varchar(1)        null,
    blocktimesheet                     varchar(1)        null,
    filelocation                       varchar(512)      null,
    terroristcheckperformed            varchar(1)        null,
    solomonupdatedate                  datetime          null,
    fieldoffice                        varchar(50)       null,
    riskassessmentscore                numeric(18, 3)    null,
    lowcipeadvocacy                    int               null,
    lowproorgdevelopment               int               null,
    lowpartnership                     int               null,
    themeaccesstoinfo                  int               null,
    themebusassocdev                   int               null,
    themecorporategovernance           int               null,
    themecorruption                    int               null,
    themedemocraticgovernance          int               null,
    themelegalregreform                int               null,
    themewomenyouth                    int               null,
    attachement                        varchar(512)      null,
    accountingquestionnairereceived    varchar(1)        null,
    fy4digit                           int               null,
    projectimpact                      varchar(15)       null,
    receivedingrantsforapproval        datetime          null,
    themeinformalsectorproperty        int               null,
    projecttypeid                      int               null,
    regionid                           int               null,
    projectstatusid                    int               null,
    donorawardid                       int               null,
    programofficer                     int               null,
    regionaldirector                   int               null,
    programassistant                   int               null,
    partnerid                          int               null,
    countryid                          int               null,
    organizationtypeid                 int               null,
    constraint pk6 primary key nonclustered (id)
)
go



if object_id('project') is not null
    print '<<< created table project >>>'
else
    print '<<< failed creating table project >>>'
go

/* 
 * table: projectkeyword 
 */

create table projectkeyword(
    id               int         identity(1, 1)        not null,
    createdby        int         null,
    lastupdatedby    int         null,
    createdate       datetime    null,
    lastupdated      datetime    null,
    keywordid        int         null,
    projectid        int         null,
    constraint pk60 primary key nonclustered (id)
)
go



if object_id('projectkeyword') is not null
    print '<<< created table projectkeyword >>>'
else
    print '<<< failed creating table projectkeyword >>>'
go

/* 
 * table: projectmodification 
 */

create table projectmodification(
    id                    int             identity(1, 1)        not null,
    previousvalue         varchar(15)     null,
    newvalue              varchar(15)     null,
    createdate            datetime        null,
    createdby             int             null,
    lastupdated           datetime        null,
    lastupdatedby         int             null,
    attachement           varchar(512)    null,
    projectid             int             null,
    modificationtypeid    int             null,
    constraint pk15 primary key nonclustered (id)
)
go



if object_id('projectmodification') is not null
    print '<<< created table projectmodification >>>'
else
    print '<<< failed creating table projectmodification >>>'
go

/* 
 * table: projectreports 
 */

create table projectreports(
    id                   int               identity(1, 1)        not null,
    reportdesc           varchar(500)      null,
    reportstartdate      datetime          null,
    reportattachemnt     varchar(512)      null,
    type                 varchar(15)       null,
    cipegrantnumber      varchar(15)       null,
    acronym              varchar(50)       null,
    reportperiod1        datetime          null,
    reportperiod2        datetime          null,
    reportdue            datetime          null,
    reportreceived       datetime          null,
    final                varchar(1)        null,
    qtr                  numeric(18, 3)    null,
    reporttogrants       datetime          null,
    receivedingrants     datetime          null,
    notation             varchar(5000)     null,
    finalperiod1         datetime          null,
    finalperiod2         datetime          null,
    finaldue             datetime          null,
    finalreceived        datetime          null,
    createdate           datetime          null,
    createdby            int               null,
    lastupdated          datetime          null,
    lastupdatedby        int               null,
    attachement          varchar(512)      null,
    approvedbyfinance    varchar(1)        null,
    projectid            int               null,
    constraint pk17 primary key nonclustered (id)
)
go



if object_id('projectreports') is not null
    print '<<< created table projectreports >>>'
else
    print '<<< failed creating table projectreports >>>'
go

/* 
 * table: projectstatus 
 */

create table projectstatus(
    id               int             identity(1, 1)        not null,
    statusname       varchar(30)     null,
    statusdesc       varchar(500)    null,
    createdate       datetime        null,
    createdby        int             null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    constraint pk46 primary key nonclustered (id)
)
go



if object_id('projectstatus') is not null
    print '<<< created table projectstatus >>>'
else
    print '<<< failed creating table projectstatus >>>'
go

/* 
 * table: projectstatuschangelog 
 */

create table projectstatuschangelog(
    id                    int         identity(1, 1)        not null,
    createdby             int         null,
    createdate            datetime    null,
    lastupdatedby         int         null,
    lastupdatedate        datetime    null,
    statuschangedate      datetime    null,
    newprojectstatusid    int         null,
    oldprojectstatusid    int         null,
    projectid             int         null,
    constraint pk65 primary key nonclustered (id)
)
go



if object_id('projectstatuschangelog') is not null
    print '<<< created table projectstatuschangelog >>>'
else
    print '<<< failed creating table projectstatuschangelog >>>'
go

/* 
 * table: projectstatustransitionsallowed 
 */

create table projectstatustransitionsallowed(
    id                             int             identity(1, 1)        not null,
    createdby                      int             null,
    createdate                     datetime        null,
    lastupdatedby                  int             null,
    lastupdatedate                 datetime        null,
    projectstatustransitiondesc    varchar(500)    null,
    startingfromstatus             int             null,
    goingtostatus                  int             null,
    constraint pk65_1 primary key nonclustered (id)
)
go



if object_id('projectstatustransitionsallowed') is not null
    print '<<< created table projectstatustransitionsallowed >>>'
else
    print '<<< failed creating table projectstatustransitionsallowed >>>'
go

/* 
 * table: projecttype 
 */

create table projecttype(
    id               int             identity(1, 1)        not null,
    projecttype      varchar(50)     null,
    projectdesc      varchar(500)    null,
    createdate       datetime        null,
    createdby        int             null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    constraint pk7 primary key nonclustered (id)
)
go



if object_id('projecttype') is not null
    print '<<< created table projecttype >>>'
else
    print '<<< failed creating table projecttype >>>'
go

/* 
 * table: region 
 */

create table region(
    id                  int             identity(1, 1)        not null,
    regionname          varchar(100)    null,
    createdate          datetime        null,
    createdby           int             null,
    lastupdated         datetime        null,
    lastupdatedby       int             null,
    regionaldirector    int             null,
    constraint pk8 primary key nonclustered (id)
)
go



if object_id('region') is not null
    print '<<< created table region >>>'
else
    print '<<< failed creating table region >>>'
go

/* 
 * table: role 
 */

create table role(
    id               int             identity(1, 1)        not null,
    rolename         varchar(100)    null,
    createdate       datetime        null,
    createdby        int             null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    constraint pk41 primary key nonclustered (id)
)
go



if object_id('role') is not null
    print '<<< created table role >>>'
else
    print '<<< failed creating table role >>>'
go

/* 
 * table: roleprivileges 
 */

create table roleprivileges(
    id              int         identity(1, 1)        not null,
    createdate      datetime    null,
    createdby       int         null,
    lastupdated     datetime    null,
    lastupdateby    int         null,
    roleid          int         null,
    constraint pk44 primary key nonclustered (id)
)
go



if object_id('roleprivileges') is not null
    print '<<< created table roleprivileges >>>'
else
    print '<<< failed creating table roleprivileges >>>'
go

/* 
 * table: stateprovince 
 */

create table stateprovince(
    id                   int            identity(1, 1)        not null,
    stateprovincecode    varchar(25)    null,
    stateprovincedesc    varchar(50)    null,
    createdate           datetime       null,
    createdby            int            null,
    lastupdated          datetime       null,
    lastupdatedby        int            null,
    countryid            int            null,
    constraint pk8_1_2 primary key nonclustered (id)
)
go



if object_id('stateprovince') is not null
    print '<<< created table stateprovince >>>'
else
    print '<<< failed creating table stateprovince >>>'
go

/* 
 * table: useractionalertschanges 
 */

create table useractionalertschanges(
    id                                  int           identity(1, 1)        not null,
    createdby                           int           null,
    createdate                          datetime      null,
    lastupdatedby                       int           null,
    lastupdated                         datetime      null,
    markedreadorunread                  varchar(1)    null,
    dateread                            datetime      null,
    actionalertchangenotificationsid    int           null,
    readby                              int           null,
    constraint pk56 primary key nonclustered (id)
)
go



if object_id('useractionalertschanges') is not null
    print '<<< created table useractionalertschanges >>>'
else
    print '<<< failed creating table useractionalertschanges >>>'
go

/* 
 * table: userinfo 
 */

create table userinfo(
    id               int               identity(1, 1)        not null,
    password         varchar(512)      null,
    loginname        varchar(50)       null,
    firstname        varchar(100)      null,
    middlename       varchar(100)      null,
    lastname         varchar(100)      null,
    email            varchar(100)      null,
    phone            varchar(20)       null,
    statuscode       varchar(30)       null,
    employeeid       varchar(25)       null,
    enddate          datetime          null,
    startdate        datetime          null,
    inactive         varchar(1)        null,
    requiredhours    numeric(18, 3)    null,
    creationdate     datetime          null,
    createdby        int               null,
    lastupdated      datetime          null,
    lastupdatedby    int               null,
    fieldofficeid    int               null,
    regionid         int               null,
    usertypeid       int               null,
    constraint pk39 primary key nonclustered (id)
)
go



if object_id('userinfo') is not null
    print '<<< created table userinfo >>>'
else
    print '<<< failed creating table userinfo >>>'
go

/* 
 * table: userrole 
 */

create table userrole(
    id               int         identity(1, 1)        not null,
    createdate       datetime    null,
    createdby        int         null,
    lastupdated      datetime    null,
    lastupdatedby    int         null,
    userinfoid       int         null,
    roleid           int         null,
    constraint pk42 primary key nonclustered (id)
)
go



if object_id('userrole') is not null
    print '<<< created table userrole >>>'
else
    print '<<< failed creating table userrole >>>'
go

/* 
 * table: usertype 
 */

create table usertype(
    id               int             identity(1, 1)        not null,
    usertypename     varchar(100)    null,
    createdate       datetime        null,
    createdby        int             null,
    lastupdated      datetime        null,
    lastupdatedby    int             null,
    constraint pk43 primary key nonclustered (id)
)
go



if object_id('usertype') is not null
    print '<<< created table usertype >>>'
else
    print '<<< failed creating table usertype >>>'
go

/* 
 * table: actionaltertchangenotifications 
 */

alter table actionaltertchangenotifications add constraint refalerttype81 
    foreign key (alerttypeid)
    references alerttype(id)
go

alter table actionaltertchangenotifications add constraint refchangenotificationtype82 
    foreign key (changetypeid)
    references changenotificationtype(id)
go


/* 
 * table: audits 
 */

alter table audits add constraint refproject73 
    foreign key (projectid)
    references project(id)
go


/* 
 * table: categorylinks 
 */

alter table categorylinks add constraint reflink104 
    foreign key (linkid)
    references link(id)
go

alter table categorylinks add constraint reflinkcategory105 
    foreign key (linkcategoryid)
    references linkcategory(id)
go


/* 
 * table: country 
 */

alter table country add constraint refuserinfo106 
    foreign key (programassistant)
    references userinfo(id)
go

alter table country add constraint refuserinfo107 
    foreign key (programofficer)
    references userinfo(id)
go

alter table country add constraint refregion42 
    foreign key (regionid)
    references region(id)
go


/* 
 * table: donor 
 */

alter table donor add constraint refcountry110 
    foreign key (countryid)
    references country(id)
go


/* 
 * table: donoraward 
 */

alter table donoraward add constraint refdonorawardstatus75 
    foreign key (donorawardstatusid)
    references donorawardstatus(id)
go

alter table donoraward add constraint refdonor1 
    foreign key (donorid)
    references donor(id)
go

alter table donoraward add constraint refdonorawardtype2 
    foreign key (donorawardtypeid)
    references donorawardtype(id)
go


/* 
 * table: donorawardmodification 
 */

alter table donorawardmodification add constraint refdonoraward78 
    foreign key (donorawardid)
    references donoraward(id)
go

alter table donorawardmodification add constraint refmodificationtype112 
    foreign key (modificationtypeid)
    references modificationtype(id)
go


/* 
 * table: donorawardstatustransitionsallowed 
 */

alter table donorawardstatustransitionsallowed add constraint refdonorawardstatus98 
    foreign key (goingtostatus)
    references donorawardstatus(id)
go

alter table donorawardstatustransitionsallowed add constraint refdonorawardstatus99 
    foreign key (startingfromstatus)
    references donorawardstatus(id)
go


/* 
 * table: fieldoffice 
 */

alter table fieldoffice add constraint refcountry108 
    foreign key (countryid)
    references country(id)
go

alter table fieldoffice add constraint refregion109 
    foreign key (regionid)
    references region(id)
go


/* 
 * table: imp 
 */

alter table imp add constraint refprojectreports77 
    foreign key (projectreportsid)
    references projectreports(id)
go


/* 
 * table: partner 
 */

alter table partner add constraint refcountry19 
    foreign key (countryid)
    references country(id)
go


/* 
 * table: payee 
 */

alter table payee add constraint refcountry76 
    foreign key (countryid)
    references country(id)
go


/* 
 * table: paymentrequest 
 */

alter table paymentrequest add constraint refpaymenttype20 
    foreign key (paymenttypeid)
    references paymenttype(id)
go

alter table paymentrequest add constraint refpayee22 
    foreign key (payeeid)
    references payee(id)
go

alter table paymentrequest add constraint refcountry23 
    foreign key (countryid)
    references country(id)
go


/* 
 * table: paymentrequestlineitem 
 */

alter table paymentrequestlineitem add constraint refproject68 
    foreign key (projectid)
    references project(id)
go

alter table paymentrequestlineitem add constraint refpaymentrequest27 
    foreign key (paymentrequestid)
    references paymentrequest(id)
go

alter table paymentrequestlineitem add constraint refaccountcode30 
    foreign key (accountcodeid)
    references accountcode(id)
go


/* 
 * table: paymentrequestmodification 
 */

alter table paymentrequestmodification add constraint refpaymentrequest111 
    foreign key (paymentrequestid)
    references paymentrequest(id)
go

alter table paymentrequestmodification add constraint refmodificationtype113 
    foreign key (modificationtypeid)
    references modificationtype(id)
go


/* 
 * table: paymentrequeststatus 
 */

alter table paymentrequeststatus add constraint refpaymentstatustype24 
    foreign key (paymentstatustypeid)
    references paymentstatustype(id)
go

alter table paymentrequeststatus add constraint refpaymentrequest25 
    foreign key (paymentrequestid)
    references paymentrequest(id)
go


/* 
 * table: paymentrequeststatustransitions 
 */

alter table paymentrequeststatustransitions add constraint refpaymentstatustype95 
    foreign key (goingtostatus)
    references paymentstatustype(id)
go

alter table paymentrequeststatustransitions add constraint refpaymentstatustype96 
    foreign key (startingfromstatus)
    references paymentstatustype(id)
go


/* 
 * table: project 
 */

alter table project add constraint refpartner70 
    foreign key (partnerid)
    references partner(id)
go

alter table project add constraint refcountry72 
    foreign key (countryid)
    references country(id)
go

alter table project add constraint reforganizationtype87 
    foreign key (organizationtypeid)
    references organizationtype(id)
go

alter table project add constraint refprojecttype7 
    foreign key (projecttypeid)
    references projecttype(id)
go

alter table project add constraint refregion8 
    foreign key (regionid)
    references region(id)
go

alter table project add constraint refprojectstatus51 
    foreign key (projectstatusid)
    references projectstatus(id)
go

alter table project add constraint refdonoraward53 
    foreign key (donorawardid)
    references donoraward(id)
go

alter table project add constraint refuserinfo54 
    foreign key (programofficer)
    references userinfo(id)
go

alter table project add constraint refuserinfo55 
    foreign key (programassistant)
    references userinfo(id)
go

alter table project add constraint refuserinfo56 
    foreign key (regionaldirector)
    references userinfo(id)
go


/* 
 * table: projectkeyword 
 */

alter table projectkeyword add constraint refkeyword83 
    foreign key (keywordid)
    references keyword(id)
go

alter table projectkeyword add constraint refproject84 
    foreign key (projectid)
    references project(id)
go


/* 
 * table: projectmodification 
 */

alter table projectmodification add constraint refproject14 
    foreign key (projectid)
    references project(id)
go

alter table projectmodification add constraint refmodificationtype114 
    foreign key (modificationtypeid)
    references modificationtype(id)
go


/* 
 * table: projectreports 
 */

alter table projectreports add constraint refproject16 
    foreign key (projectid)
    references project(id)
go


/* 
 * table: projectstatuschangelog 
 */

alter table projectstatuschangelog add constraint refproject89 
    foreign key (projectid)
    references project(id)
go

alter table projectstatuschangelog add constraint refprojectstatus90 
    foreign key (oldprojectstatusid)
    references projectstatus(id)
go

alter table projectstatuschangelog add constraint refprojectstatus91 
    foreign key (newprojectstatusid)
    references projectstatus(id)
go


/* 
 * table: projectstatustransitionsallowed 
 */

alter table projectstatustransitionsallowed add constraint refprojectstatus92 
    foreign key (startingfromstatus)
    references projectstatus(id)
go

alter table projectstatustransitionsallowed add constraint refprojectstatus94 
    foreign key (goingtostatus)
    references projectstatus(id)
go


/* 
 * table: region 
 */

alter table region add constraint refuserinfo103 
    foreign key (regionaldirector)
    references userinfo(id)
go


/* 
 * table: roleprivileges 
 */

alter table roleprivileges add constraint refrole48 
    foreign key (roleid)
    references role(id)
go


/* 
 * table: stateprovince 
 */

alter table stateprovince add constraint refcountry88 
    foreign key (countryid)
    references country(id)
go


/* 
 * table: useractionalertschanges 
 */

alter table useractionalertschanges add constraint refactionaltertchangenotifications79 
    foreign key (actionalertchangenotificationsid)
    references actionaltertchangenotifications(id)
go

alter table useractionalertschanges add constraint refuserinfo80 
    foreign key (readby)
    references userinfo(id)
go


/* 
 * table: userinfo 
 */

alter table userinfo add constraint reffieldoffice43 
    foreign key (fieldofficeid)
    references fieldoffice(id)
go

alter table userinfo add constraint refregion46 
    foreign key (regionid)
    references region(id)
go

alter table userinfo add constraint refusertype47 
    foreign key (usertypeid)
    references usertype(id)
go


/* 
 * table: userrole 
 */

alter table userrole add constraint refuserinfo44 
    foreign key (userinfoid)
    references userinfo(id)
go

alter table userrole add constraint refrole45 
    foreign key (roleid)
    references role(id)
go


